# /code/db_ops.py
import os
import pymysql
import datetime
from code.logger_config import logger
from dotenv import load_dotenv

# 加载环境变量
load_dotenv()


def get_db_connection():
    """创建并返回数据库连接"""
    return pymysql.connect(
        host=os.getenv('DB_HOST'),
        user=os.getenv('DB_USER'),
        password=os.getenv('DB_PASSWORD'),
        database=os.getenv('DB_NAME'),
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )


def insert_into_staff(user_id, name, dept_id, online_status, serving=True, latest_login=None):
    """将用户数据插入staff表"""
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        # 转换online_status为字符串格式
        online_status_str = 'true' if online_status else 'false'
        serving_int = 1 if serving else 0

        # 处理latest_login
        latest_login_value = latest_login if latest_login else '2000-01-01 00:00:00'

        sql = """
            INSERT INTO staff 
                (user_id, name, dept_id, online_status, serving, created_at, updated_at, latest_login) 
            VALUES 
                (%s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE 
                name = VALUES(name),
                dept_id = VALUES(dept_id),
                online_status = VALUES(online_status),
                serving = VALUES(serving),
                updated_at = VALUES(updated_at),
                latest_login = IF(VALUES(latest_login) != '2000-01-01 00:00:00', VALUES(latest_login), latest_login)
        """

        cursor.execute(sql, (
            user_id,
            name,
            dept_id,
            online_status_str,
            serving_int,
            current_time,
            current_time,
            latest_login_value
        ))
        conn.commit()
        return True
    except Exception as e:
        logger.error(f"数据库操作失败: {str(e)}")
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def get_serving_staff_data():
    """获取staff表中serving=true的user_id和online_status"""
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT user_id, online_status FROM staff WHERE serving = TRUE")
        return cursor.fetchall()
    except Exception as e:
        logger.error(f"获取serving_staff数据失败: {str(e)}")
        return []
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def get_retired_staff_data():
    """获取staff表中serving=false的user_id和online_status"""
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT user_id, online_status FROM staff WHERE serving = FALSE")
        return cursor.fetchall()
    except Exception as e:
        logger.error(f"获取retired_staff数据失败: {str(e)}")
        return []
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def update_staff_updated_at(user_id, current_time):
    """更新退休员工的updated_at字段"""
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        sql = "UPDATE staff SET updated_at = %s WHERE user_id = %s"
        cursor.execute(sql, (current_time, user_id))
        conn.commit()
        return cursor.rowcount > 0
    except Exception as e:
        logger.error(f"更新退休员工时间失败: {str(e)}")
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def get_staff_with_unknown_gender_or_position():
    """获取gender或position为'未知'的user_id"""
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        sql = """
            SELECT user_id 
            FROM staff 
            WHERE gender = '未知' OR position = '未知'
        """
        cursor.execute(sql)
        return [row['user_id'] for row in cursor.fetchall()]
    except Exception as e:
        logger.error(f"获取未知性别/职位员工失败: {str(e)}")
        return []
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()


def update_staff_gender_position(user_id, gender, position):
    """更新员工的性别和职位信息"""
    conn = None
    cursor = None
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        sql = """
            UPDATE staff 
            SET gender = %s, 
                position = %s, 
                updated_at = %s 
            WHERE user_id = %s
        """
        cursor.execute(sql, (gender, position, current_time, user_id))
        conn.commit()
        return cursor.rowcount > 0
    except Exception as e:
        logger.error(f"更新员工性别职位失败: {str(e)}")
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()